package edu.colorado.karl.databases;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

// TODO: change setpoints table to look like:
//	Time  <setpoint 1>   <setpoint 2>   ...
/**
 * Abstraction of the Setpoints Database.  SetpointsDatabase functions as the
 * bridge to the MySQL databases storing the system's set points.
 * @author Michael Knudson
 * @version 5/4/08
 */
public class SetpointsDatabase {
	//	Used for log4j logging
	private static final Log log = LogFactory.getLog(SetpointsDatabase.class);
	
	/**
	 * Database room temperature identifier.
	 */
	public static final String ROOM_TEMP = "roomTemp";
	/**
	 * Database room temperature differential identifier.
	 */
	public static final String ROOM_RANGE = "roomRange";
	/**
	 * Database Hot Tank set point identifier.
	 */
	public static final String HOT_TANK_SP = "hotTank";
	/**
	 * Database Hot Tank set point differential identifier.
	 */
	public static final String HOT_TANK_RANGE = "hotTankRange";
	/**
	 * Database Cold Tank set point identifier.
	 */
	public static final String COLD_TANK_SP = "coldTank";
	/**
	 * Database Cold Tank set point differential identifier.
	 */
	public static final String COLD_TANK_RANGE = "coldTankRange";
	/**
	 * Database Heating/Cooling mode identifier.  true if we are heating.
	 */
	public static final String HEATING_MODE = "heatingMode";
	/**
	 * Database Manual/Automatic mode identifier.  true if we are in automatic mode.
	 */
	public static final String MODE = "mode";
	
	public static final String AUTO_HOT_TANK = "autoHotTank";
	
	public static final String AUTO_COLD_TANK= "autoColdTank";
	
	//	Variables for caching information
	
	// Time cache was last updated, in ms
	private long lastUpdate = 0;
	// How many ms to hold the cache
	private static final int CACHE_EXPIRE_TIME = 5000;
	private int roomTempCache;
	private int roomRangeCache;
	private int hotTankTempCache;
	private int hotTankRangeCache;
	private int coldTankTempCache;
	private int coldTankRangeCache;
	private int autoHotTankCache;
	private int autoColdTankCache;

	/**
	 * Sole constructor.
	 */
	public SetpointsDatabase() {
		
	}
	
	/**
	 * Retrieves the heating/cooling mode.
	 * @return <code>true</code> if we are in heating mode, 
	 * <code>false</code> if we are cooling.
	 */
	public boolean getHeatingMode() {
		Connection c = SetpointsDatabaseConnector.getConnection();
		int ret = 1;

		if(c == null) {
			return true;	// Return empty list, error already logged by connector
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("SELECT * FROM Setpoints WHERE name = \"" + HEATING_MODE + "\";");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				ret = rs.getInt(2);
			}
		} catch (SQLException e) {
			log.warn("SPDB: SQL Exception", e);
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		
		if(ret == 1)
			return true;
		return false;
	}
	
	/**
	 * Retrieves the system mode.
	 * @return <code>true</code>  if we are using IntelliCORE's set points, 
	 * <code>false</code> if we are in manual mode.
	 */
	public boolean getMode() {
		Connection c = SetpointsDatabaseConnector.getConnection();
		int ret = 1;

		if(c == null) {
			return true;	// Return empty list, error already logged by connector
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("SELECT * FROM Setpoints WHERE name = \"" + MODE + "\";");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				ret = rs.getInt(2);
			}
		} catch (SQLException e) {
			log.warn("SPDB: SQL Exception", e);
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		
		if(ret == 1)
			return true;
		return false;
	}
	
	/**
	 * Retrieves the set points for room temperature.
	 * @return a list of set points.
	 * @see edu.colorado.karl.devices.RoomState
	 */
	public ArrayList<SPResponse> getRoomSetPoints() {
		// Get current time
		long curTime = Calendar.getInstance().getTimeInMillis();
		
		if(curTime - lastUpdate > CACHE_EXPIRE_TIME) {
			boolean success = updateCache();
			
			//	If caching isn't working attempt the specific query
			if(!success)
				return getRoomSetPointsNoCache();
		}

		//	Use the cached values
		ArrayList<SPResponse> l = new ArrayList<SPResponse>();
		l.add(new SPResponse(ROOM_TEMP, roomTempCache));
		l.add(new SPResponse(ROOM_RANGE, roomRangeCache));
		return l;
	}

	private ArrayList<SPResponse> getRoomSetPointsNoCache() {
		// List of responses
		ArrayList<SPResponse> l = new ArrayList<SPResponse>();
		// Database connection
		Connection c = SetpointsDatabaseConnector.getConnection();

		if(c == null) {
			return l;	// Return empty list, error already logged by connector
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("SELECT * FROM Setpoints " +
					"WHERE name = \"" + ROOM_TEMP + "\" OR " + 
					"name = \"" + ROOM_RANGE + "\";");
			ResultSet rs = s.getResultSet();
			
			while(rs.next()) {
				l.add(new SPResponse(rs.getString(1), rs.getInt(2)));
			}
		} catch (SQLException e) {
			log.warn("SPDB: SQL Exception", e);
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the set points for the hot and cold tanks.
	 * @return a list of set points.
	 * @see edu.colorado.karl.devices.WaterTanks
	 */
	public ArrayList<SPResponse> getTankSetPoints() {
		// Get current time
		long curTime = Calendar.getInstance().getTimeInMillis();
		
		if(curTime - lastUpdate > CACHE_EXPIRE_TIME) {
			boolean success = updateCache();
			
			//	If caching isn't working attempt the specific query
			if(!success)
				return getTankSetPointsNoCache();
		}

		//	Use the cached values
		ArrayList<SPResponse> l = new ArrayList<SPResponse>();
		l.add(new SPResponse(HOT_TANK_SP, hotTankTempCache));
		l.add(new SPResponse(HOT_TANK_RANGE, hotTankRangeCache));
		l.add(new SPResponse(COLD_TANK_SP, coldTankTempCache));
		l.add(new SPResponse(COLD_TANK_RANGE, coldTankRangeCache));
		l.add(new SPResponse(AUTO_HOT_TANK, autoHotTankCache));
		l.add(new SPResponse(AUTO_COLD_TANK, autoColdTankCache));
		return l;
	}

	private ArrayList<SPResponse> getTankSetPointsNoCache() {
		// List of responses
		ArrayList<SPResponse> l = new ArrayList<SPResponse>();
		// Database connection
		Connection c = SetpointsDatabaseConnector.getConnection();

		if(c == null) {
			log.warn("Setpoints Database Connection failed to open.");
		}
				
		try {
			Statement s = c.createStatement();
			s.executeQuery("SELECT * FROM Setpoints " +
					"WHERE name = \"" + HOT_TANK_SP + "\" OR " + 
					"name = \"" + HOT_TANK_RANGE + "\" OR " +
					"name = \"" + COLD_TANK_SP + "\" OR " +
					"name = \"" + COLD_TANK_RANGE + "\";");
			ResultSet rs = s.getResultSet();
			
			while(rs.next()) {
				l.add(new SPResponse(rs.getString(1), rs.getInt(2)));
			}
		} catch (SQLException e) {
			log.warn("SPDB: SQL Exception", e);
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Updates a mode in the Setpoints database.
	 * @param modeType The name of the set point to update.  This should be
	 * one of the publicly declared constants in this class.
	 * @param value The new value for the mode.  See the constant description
	 * for information on which value to use.
	 * @return <code>true</code> if the update was successful.
	 */
	public boolean updateMode(String modeType, boolean value) {
		if(value)
			return updateSetPoint(modeType, 1);
		return updateSetPoint(modeType, 0);
	}
	
	/**
	 * Updates a value in the Setpoints database.
	 * @param setpoint The name of the set point to update.  This should be one
	 * of the publicly declared constants in this class.
	 * @param value The new value for the setpoint.
	 * @return <code>true</code> if the update was successful.
	 */
	public boolean updateSetPoint(String setpoint, int value) {
		// Database connection
		Connection c = SetpointsDatabaseConnector.getConnection();
		
		if(c == null) {
			log.warn("Setpoints Database Connection failed to open.");
			return false;
		}

		try {
			Statement s = c.createStatement();
			s.executeUpdate("UPDATE Setpoints SET value = '" + value + "' " +
					"WHERE name = '" + setpoint + "';");			
		} catch (Exception e) {
			log.warn("SPDB: SQL Exception", e);
			return false;
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		
		// Need to reset the cache because values changed
		lastUpdate = 0;

		return true;
	}
	
	/**
	 * Attempts to update all cache values.  If something fails, the cache is
	 * flagged as unusable until the next attempt.
	 * @return boolean indicating success of the update.
	 */
	private boolean updateCache() {
		// Database connection
		Connection c = SetpointsDatabaseConnector.getConnection();
		boolean updateFlag[] = new boolean[6];
		
		for (int i = 0; i < updateFlag.length; i++) {
			updateFlag[i] = false;
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("SELECT * FROM Setpoints;");
			ResultSet rs = s.getResultSet();
			
			while(rs.next()) {
				if(rs.getString(1).equals(ROOM_TEMP)) {
					if(updateFlag[0])
						return failCacheUpdate(c);
					
					roomTempCache = rs.getInt(2);
					updateFlag[0] = true;
				}
				else if(rs.getString(1).equals(ROOM_RANGE)) {
					if(updateFlag[1])
						return failCacheUpdate(c);
					
					roomRangeCache = rs.getInt(2);
					updateFlag[1] = true;					
				}
				else if(rs.getString(1).equals(HOT_TANK_SP)) {
					if(updateFlag[2])
						return failCacheUpdate(c);
					
					hotTankTempCache = rs.getInt(2);
					updateFlag[2] = true;
				}
				else if(rs.getString(1).equals(HOT_TANK_RANGE)) {
					if(updateFlag[3])
						return failCacheUpdate(c);
					
					hotTankRangeCache = rs.getInt(2);
					updateFlag[3] = true;
				}
				else if(rs.getString(1).equals(COLD_TANK_SP)) {
					if(updateFlag[4])
						return failCacheUpdate(c);
					
					coldTankTempCache = rs.getInt(2);
					updateFlag[4] = true;
				}
				else if(rs.getString(1).equals(COLD_TANK_RANGE)) {
					if(updateFlag[5])
						return failCacheUpdate(c);
					
					coldTankRangeCache = rs.getInt(2);
					updateFlag[5] = true;
				}
				else if(rs.getString(1).equals(ROOM_RANGE)) {
					if(updateFlag[1])
						return failCacheUpdate(c);
					
					roomRangeCache = rs.getInt(2);
					updateFlag[1] = true;					
				}
				else if(rs.getString(1).equals(HOT_TANK_SP)) {
					if(updateFlag[2])
						return failCacheUpdate(c);
					
					hotTankTempCache = rs.getInt(2);
					updateFlag[2] = true;
				}
			}
		} catch (SQLException e) {
			log.warn("SPDB: SQL Exception", e);
			return failCacheUpdate(c);
		}

		//	Check ALL values were updated, if not, fail
		for (int i = 0; i < updateFlag.length; i++) {
			if(updateFlag[i] == false)
				return failCacheUpdate(c);
		}
		
		SetpointsDatabaseConnector.closeConnection(c);
		lastUpdate = Calendar.getInstance().getTimeInMillis();
		return true;
	}
	
	//	Cleanup code for a failed cache update
	private boolean failCacheUpdate(Connection c) {
		lastUpdate = 0;
		log.debug("SPDB: Cache update failed.");
		SetpointsDatabaseConnector.closeConnection(c);
		return false;
	}
}
